using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Clanta.Core.Domain;
using Clanta.Data;
using System.Collections;
using System.Data;

namespace Clanta.ImportJAOO
{
    class Program
    {
        private static readonly string SessionFactoryConfigPath = @"C:\projects\Clanta\src\Clanta.Web\Config\ClantaNHibernate.config";

        static void Main(string[] args)
        {
            Program p = new Program();
            p.Import("qcon-sanfran");

        }

        private void Import(string logicalConferenceName)
        {
            long conferenceId;
            string conferenceName;
            DataTable trackTable;
            DataTable presentationTable;
            DataTable speakerTable;
            DataTable speakerPresentationTable;

            MySqlConnection conn = new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MySql"].ConnectionString);
            conn.Open();
            try
            {
                MySqlTransaction tx = conn.BeginTransaction();

                Console.WriteLine("Initializing conference data");
                InitConferenceData(conn, logicalConferenceName, out conferenceId, out conferenceName);
                Console.WriteLine("Initializing track data");
                trackTable = InitTrackData(conn, conferenceId);
                Console.WriteLine("Initializing presentation data");
                presentationTable = InitPresentationData(conn, conferenceId);
                Console.WriteLine("Initializing speaker data");
                speakerTable = InitSpeakerData(conn, conferenceId);
                Console.WriteLine("Initializing relations betweens speakers and presentations");
                speakerPresentationTable = InitSpeakerPresentationTable(conn, conferenceId);

                tx.Commit();
            }
            finally
            {
                conn.Close();
            }

            NHibernateDaoFactory daoFactory = new NHibernateDaoFactory(SessionFactoryConfigPath);
            Customer customer = daoFactory.CustomerDao.GetUniqueByExample(new Customer("Trifork A/S"));
            if (customer == null)
            {
                customer = new Customer("Trifork A/S");
                daoFactory.CustomerDao.Save(customer);
            }
            Conference conference = new Conference(conferenceName, customer);

            Dictionary<long, Track> oidTrackMap = new Dictionary<long, Track>();
            foreach (DataRow trackRow in trackTable.Rows)
            {
                Track track = new Track((string)trackRow["name"], conference);
                conference.Tracks.Add(track);
                oidTrackMap.Add((long)trackRow["oid"], track);
            }

            Dictionary<long, Presentation> oidPresentationMap = new Dictionary<long, Presentation>();
            foreach (DataRow presentationRow in presentationTable.Rows)
            {
                Presentation presentation = new Presentation((string)presentationRow["title"], conference);
                if (!presentationRow.IsNull("startDate"))
                {
                    presentation.PresentationStart = (DateTime?)presentationRow["startDate"];
                }
                if (!presentationRow.IsNull("track_oid"))
                {
                    presentation.Track = oidTrackMap[(long)presentationRow["track_oid"]];
                }
                conference.Presentations.Add(presentation);
                oidPresentationMap.Add((long)presentationRow["oid"], presentation);
            }

            Dictionary<long, Speaker> oidSpeakerMap = new Dictionary<long, Speaker>();
            foreach (DataRow speakerRow in speakerTable.Rows)
            {
                Speaker speaker = new Speaker((string)speakerRow["name"], conference);
                speaker.Email = (string)speakerRow["email"];
                conference.Speakers.Add(speaker);
                oidSpeakerMap.Add((long)speakerRow["oid"], speaker);
            }

            foreach (DataRow speakerPresentationRow in speakerPresentationTable.Rows)
            {
                long presentationId = (long)speakerPresentationRow["presentation_id"];
                long speakerId = (long)speakerPresentationRow["speaker_id"];

                Speaker speaker = oidSpeakerMap[speakerId];
                Presentation presentation = oidPresentationMap[presentationId];

                speaker.Presentations.Add(presentation);
                presentation.Speakers.Add(speaker);
            }

            daoFactory.ConferenceDao.Save(conference);
            daoFactory.ConferenceDao.CommitChanges();
        }

        private void InitConferenceData(MySqlConnection conn, string logicalConferenceName, out long conferenceId, out string conferenceName)
        {
            MySqlCommand cmd = new MySqlCommand(string.Format("SELECT oid, intratoolsPrefix FROM conference WHERE name='{0}'", logicalConferenceName), conn);
            MySqlDataReader reader = cmd.ExecuteReader();
            try
            {
                if (reader.Read())
                {
                    conferenceId = reader.GetInt64("oid");
                    conferenceName = reader.GetString("intratoolsPrefix");
                }
                else
                {
                    throw new Exception(string.Format("Conference '{0}' not found", logicalConferenceName));
                }
            }
            finally
            {
                reader.Close();
            }
        }

        private DataTable InitPresentationData(MySqlConnection conn, long conferenceId)
        {
            // Only fetch the earliest start date
            MySqlCommand cmd =
                new MySqlCommand(
                    string.Format(
                        @"SELECT e.oid, e.title, e.track_oid, MIN(s.startDate) AS startDate
                          FROM event e
                          LEFT JOIN event_slot es ON e.oid=es.event_id
                          LEFT JOIN slot s ON s.oid=es.slot_id
                          WHERE e.conference_oid='{0}' AND type='dk.eos.jaoo.model.hibernate.PresentationImpl'
                          GROUP BY e.oid, s.startDate",
                        conferenceId),
                    conn);
            return LoadTable(cmd);
        }

        private DataTable InitTrackData(MySqlConnection conn, long conferenceId)
        {
            MySqlCommand cmd =
                new MySqlCommand(
                    string.Format(
                        @"SELECT oid, name
                          FROM track
                          WHERE conference_oid='{0}'",
                        conferenceId),
                conn);
            return LoadTable(cmd);
        }

        private DataTable InitSpeakerData(MySqlConnection conn, long conferenceId)
        {
            MySqlCommand cmd =
                new MySqlCommand(
                    string.Format(
                        @"SELECT oid, CONCAT(TRIM(CONCAT(firstname, ' ', middlename)), ' ', lastname) as name, email
                          FROM speaker
                          WHERE conference_oid='{0}'",
                        conferenceId),
                    conn);
            return LoadTable(cmd);
        }

        private DataTable InitSpeakerPresentationTable(MySqlConnection conn, long conferenceId)
        {
            MySqlCommand cmd =
                new MySqlCommand(
                    string.Format(
                        @"SELECT DISTINCT sp.presentation_id, sp.speaker_id
                          FROM speaker_pres sp, event e
                          WHERE sp.presentation_id=e.oid
                            AND e.conference_oid='{0}'
                            AND e.type='dk.eos.jaoo.model.hibernate.PresentationImpl'",
                        conferenceId),
                    conn);
            return LoadTable(cmd);
        }

        private DataTable LoadTable(MySqlCommand cmd)
        {
            MySqlDataReader reader = cmd.ExecuteReader();
            try
            {
                DataTable resultTable = new DataTable();
                resultTable.Load(reader);
                return resultTable;
            }
            finally
            {
                reader.Close();
            }
        }
    }
}
